package cn.springboot.osbulkparts.service.impl;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.security.core.Authentication;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.util.UriUtils;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.github.pagehelper.util.StringUtil;

import cn.springboot.osbulkparts.common.CommonBusinessException;
import cn.springboot.osbulkparts.common.CommonConstantEnum;
import cn.springboot.osbulkparts.common.CommonResultInfo;
import cn.springboot.osbulkparts.common.OSLanguage;
import cn.springboot.osbulkparts.common.entity.CommonEntity;
import cn.springboot.osbulkparts.common.security.entity.SecurityUserInfoEntity;
import cn.springboot.osbulkparts.common.utils.CommonSqlUtils;
import cn.springboot.osbulkparts.common.utils.excel.CommonExcelConfig;
import cn.springboot.osbulkparts.common.utils.excel.CommonPoiReadUtil;
import cn.springboot.osbulkparts.config.i18n.I18nMessageBean;
import cn.springboot.osbulkparts.dao.system.TDictDataDao;
import cn.springboot.osbulkparts.dao.user.MRoleInfoDao;
import cn.springboot.osbulkparts.dao.warehouse.MPackingDetailsInfoDao;
import cn.springboot.osbulkparts.entity.MPackingDetailsInfoEntity;
import cn.springboot.osbulkparts.entity.MRoleInfoEntity;
import cn.springboot.osbulkparts.entity.TDictDataEntity;
import cn.springboot.osbulkparts.entity.TOrderInfoEntity;
import cn.springboot.osbulkparts.service.PackingDetailsListService;

@Service
public class PackingDetailsListServiceImpl implements PackingDetailsListService {

	@Autowired
	private MPackingDetailsInfoDao mPackingDetailsInfoDao;
	@Autowired
	private TDictDataDao tDictDataDao;
	@Autowired
	private MRoleInfoDao mroleInfoDao;
	@Autowired
	private I18nMessageBean messageBean;

	@Override
	public CommonResultInfo<Map<String, List<TDictDataEntity>>> initViews(String lang, Locale locale) {
		messageBean.setLocale(null,null,locale);
		CommonResultInfo<Map<String, List<TDictDataEntity>>> result = new CommonResultInfo<Map<String, List<TDictDataEntity>>>();
		try {
			Map<String,List<TDictDataEntity>> map = new HashMap<>();
			TDictDataEntity tDictDataEntity = new TDictDataEntity();
			tDictDataEntity.setDictTypeCode("unit");
			tDictDataEntity.setLanguageFlag(OSLanguage.localeToTableSuffix(lang));
			map.put("unit",tDictDataDao.selectByPrimaryKey(tDictDataEntity));
			tDictDataEntity.setDictTypeCode("transportation");
			map.put("shippingMethods",tDictDataDao.selectByPrimaryKey(tDictDataEntity));
			result.setResult(map);
		} catch (Exception e) {
			result.setCode(ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build().getStatusCodeValue());
			result.setMessage(messageBean.getMessage("common.server.error"));
			result.setException(e.getMessage().toString());
		} finally {
			return result;
		}
	}

	@Override
	public CommonResultInfo<?> getPackingDetailsList(MPackingDetailsInfoEntity mPackingDetailsInfoEntity, int pageNum, int pageSize, Authentication auth, Locale locale) {
		messageBean.setLocale(null,null,locale);
		CommonResultInfo<MPackingDetailsInfoEntity> result = new CommonResultInfo<MPackingDetailsInfoEntity>();
		try {
			PageHelper.startPage(pageNum, pageSize);
			PageInfo<MPackingDetailsInfoEntity> pageInfo = new PageInfo<>(
					mPackingDetailsInfoDao.selectPackingDetailsInfoList(mPackingDetailsInfoEntity));
			result.setCode(ResponseEntity.ok().build().getStatusCodeValue());
			result.setResultInfo(pageInfo);
		} catch (Exception e) {
			e.printStackTrace();
			result.setCode(ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build().getStatusCodeValue());
			result.setMessage(messageBean.getMessage("common.server.error"));
			result.setException(e.getMessage().toString());
		} finally {
			return result;
		}
	}

	@Override
	public CommonResultInfo<?> getPackingDetailsInfo(MPackingDetailsInfoEntity mPackingDetailsInfoEntity, Locale locale) {
		messageBean.setLocale(null,null,locale);
		CommonResultInfo<MPackingDetailsInfoEntity> result = new CommonResultInfo<MPackingDetailsInfoEntity>();
		try {
			List<MPackingDetailsInfoEntity> resultList = mPackingDetailsInfoDao.selectPackingDetailsInfoList(mPackingDetailsInfoEntity);
			result.setCode(ResponseEntity.ok().build().getStatusCodeValue());
			if(resultList.size()>0) {
				result.setResult(resultList.get(0));
			}
			else {
				result.setCode(ResponseEntity.badRequest().build().getStatusCodeValue());
				result.setMessage(messageBean.getMessage("common.info.empty"));
			}
		} catch (Exception e) {
			e.printStackTrace();
			result.setCode(ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build().getStatusCodeValue());
			result.setMessage(messageBean.getMessage("common.server.error"));
			result.setException(e.getMessage().toString());
		} finally {
			return result;
		}
	}

	@Override
	public CommonResultInfo<?> addPackingDetailsInfo(MPackingDetailsInfoEntity mPackingDetailsInfoEntity, Authentication auth, Locale locale) {
		messageBean.setLocale(null,null,locale);
		CommonResultInfo<?> result = new CommonResultInfo<MPackingDetailsInfoEntity>();
		result.setCode(ResponseEntity.badRequest().build().getStatusCodeValue());
		try {
			SecurityUserInfoEntity principal = (SecurityUserInfoEntity)auth.getPrincipal();
			MRoleInfoEntity roleInfoEntity = mroleInfoDao.selectRoleInfo(principal.getRoleIdSelected(),mPackingDetailsInfoEntity.getLanguageFlag());
			mPackingDetailsInfoEntity.setId(CommonSqlUtils.getUUID32());
			mPackingDetailsInfoEntity.setCreateUser(principal.getUserName());
			mPackingDetailsInfoEntity.setIsDelete(0);
			mPackingDetailsInfoEntity.setVersion(1);
			int returnInt = mPackingDetailsInfoDao.insertSelective(mPackingDetailsInfoEntity);
			if (returnInt > 0) {
				result.setCode(ResponseEntity.status(HttpStatus.CREATED).build().getStatusCodeValue());
				result.setMessage(messageBean.getMessage("common.add.success", CommonConstantEnum.PACKINGDETAILS.getTypeName(locale)));
			}
		} catch (Exception e) {
			e.printStackTrace();
			result.setCode(ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build().getStatusCodeValue());
			result.setMessage(messageBean.getMessage("common.server.error"));
			result.setException(e.getMessage().toString());
		} finally {
			return result;
		}
	}

	@Override
	public CommonResultInfo<?> updatePackingDetailsInfo(MPackingDetailsInfoEntity mPackingDetailsInfoEntity, Authentication auth, Locale locale) {
		messageBean.setLocale(null,null,locale);
		CommonResultInfo<?> result = new CommonResultInfo<MPackingDetailsInfoEntity>();
		result.setCode(ResponseEntity.badRequest().build().getStatusCodeValue());
		SecurityUserInfoEntity principal = (SecurityUserInfoEntity)auth.getPrincipal();
		try {
			List<MPackingDetailsInfoEntity> MPackingDetailsInfoList = mPackingDetailsInfoDao.selectPackingDetailsInfoList(mPackingDetailsInfoEntity);
			if(MPackingDetailsInfoList.size() != 0 && !(MPackingDetailsInfoList.get(0).getVersion().equals(mPackingDetailsInfoEntity.getVersion()) )) {
				result.setMessage(messageBean.getMessage("common.update.version", CommonConstantEnum.PACKINGDETAILS.getTypeName(locale)));
			}else {
				mPackingDetailsInfoEntity.setUpdateUser(principal.getUserName());
				mPackingDetailsInfoEntity.setVersion(mPackingDetailsInfoEntity.getVersion()+1);
				mPackingDetailsInfoEntity.setIsDelete(0);
				int returnInt = mPackingDetailsInfoDao.updateByPrimaryKeySelective(mPackingDetailsInfoEntity);
				if (returnInt > 0) {
					result.setCode(ResponseEntity.status(HttpStatus.CREATED).build().getStatusCodeValue());
					result.setMessage(messageBean.getMessage("common.update.success", CommonConstantEnum.PACKINGDETAILS.getTypeName(locale)));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			result.setCode(ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build().getStatusCodeValue());
			result.setMessage(messageBean.getMessage("common.server.error"));
			result.setException(e.getMessage().toString());
		} finally {
			return result;
		}
	}

	@Override
	public CommonResultInfo<?> importExcel(MultipartFile excleFile, HttpServletRequest request, Authentication auth, String lang, Locale locale) {
		messageBean.setLocale(null,null,locale);
		CommonResultInfo<?> result = new CommonResultInfo<MPackingDetailsInfoEntity>();
		result.setCode(ResponseEntity.badRequest().build().getStatusCodeValue());
		try {
			List<MPackingDetailsInfoEntity> stockInfoParams = resolvExcelToDb(excleFile,auth,OSLanguage.localeToTableSuffix(lang),locale);
			if(stockInfoParams.size() == 0) {
				result.setMessage(messageBean.getMessage("common.excel.error"));
			}else {
//				tstockInfoDao.deleteBatachByNo(stockInfoParams);
				int resultInt = mPackingDetailsInfoDao.importExcelData(stockInfoParams);
				if(resultInt >0) {
					result.setCode(ResponseEntity.status(HttpStatus.CREATED).build().getStatusCodeValue());
					result.setMessage(messageBean.getMessage("common.excel.success"));
				}
			}
		} catch (NullPointerException se) {
			result.setCode(ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build().getStatusCodeValue());
			result.setMessage(se.getMessage().toString());
		} catch (Exception e) {
			result.setCode(ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build().getStatusCodeValue());
			result.setMessage(messageBean.getMessage("common.server.error"));
			result.setException(e.getMessage().toString());
		} finally {
			return result;
		}
	}

	@Override
	public CommonResultInfo<?> deleteBatchByIds(CommonEntity commonEntity, Authentication auth, Locale locale) {
		messageBean.setLocale(null,null,locale);
		CommonResultInfo<?> result = new CommonResultInfo<TOrderInfoEntity>();
		result.setCode(ResponseEntity.badRequest().build().getStatusCodeValue());
		SecurityUserInfoEntity principal = (SecurityUserInfoEntity)auth.getPrincipal();
		try {
			int returnInt = mPackingDetailsInfoDao.deliverReceivingDelete(commonEntity.getIdsStr(),principal.getUserName(),CommonConstantEnum.TO_DELETE.getTypeName(locale));
			if (returnInt > 0) {
				result.setMessage(messageBean.getMessage("common.delete.success", CommonConstantEnum.PACKINGDETAILS.getTypeName(locale)));
				result.setCode(ResponseEntity.status(HttpStatus.CREATED).build().getStatusCodeValue());
			}
		} catch (Exception e) {
			e.printStackTrace();
			result.setCode(ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build().getStatusCodeValue());
			result.setMessage(messageBean.getMessage("common.server.error"));
			result.setException(e.getMessage().toString());
		} finally {
			return result;
		}
	}

	@Override
	public ResponseEntity<byte[]> downloadExcel(MPackingDetailsInfoEntity mPackingDetailsInfoEntity, Locale locale) {
		messageBean.setLocale(null,null,locale);
		String[] title = messageBean.getMessage("file.title.packingDetail").split(",");
		List<MPackingDetailsInfoEntity> resultList = mPackingDetailsInfoDao.selectPackingDetailsInfoList(mPackingDetailsInfoEntity);
		ResponseEntity<byte[]> result = educeExcel(title,resultList,locale);
		return result;
	}

	@Override
	public CommonResultInfo<?> getPackingDetailsInfoBymaterialCode(MPackingDetailsInfoEntity mPackingDetailsInfoEntity, Locale locale) {
		messageBean.setLocale(null,null,locale);
		CommonResultInfo<MPackingDetailsInfoEntity> result = new CommonResultInfo<MPackingDetailsInfoEntity>();
		try {
			List<MPackingDetailsInfoEntity> resultInfo = mPackingDetailsInfoDao.getPackingDetailsInfoBymaterialCode(mPackingDetailsInfoEntity.getMaterialCode());
			result.setCode(ResponseEntity.ok().build().getStatusCodeValue());
			if(resultInfo.size()>0) {
				result.setResult(resultInfo.get(0));
			}
		} catch (Exception e) {
			e.printStackTrace();
			result.setCode(ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build().getStatusCodeValue());
			result.setMessage(messageBean.getMessage("common.server.error"));
			result.setException(e.getMessage().toString());
		} finally {
			return result;
		}
	}

	@Override
	public CommonResultInfo<?> updatePackingDetailsList(CommonEntity commonEntity, Authentication auth, Locale locale) {
		messageBean.setLocale(null,null,locale);
		CommonResultInfo<?> result = new CommonResultInfo<MPackingDetailsInfoEntity>();
		result.setCode(ResponseEntity.badRequest().build().getStatusCodeValue());
		SecurityUserInfoEntity principal = (SecurityUserInfoEntity)auth.getPrincipal();
		try {
			int returnInt = mPackingDetailsInfoDao.updatePackingDetailsList(commonEntity,principal.getUserName());
			if (returnInt > 0) {
				result.setCode(ResponseEntity.status(HttpStatus.CREATED).build().getStatusCodeValue());
				result.setMessage(messageBean.getMessage("common.update.success", CommonConstantEnum.PACKINGDETAILS.getTypeName(locale)));
			}

		} catch (Exception e) {
			e.printStackTrace();
			result.setCode(ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build().getStatusCodeValue());
			result.setMessage(messageBean.getMessage("common.server.error"));
			result.setException(e.getMessage().toString());
		} finally {
			return result;
		}
	}

	/****Private Methods****/
	/**
	 *
	 * @param titles 第一列名
	 * @param list 向单元格插入数据
	 * @return
	 */
	private ResponseEntity<byte[]> educeExcel(String[] titles,List<MPackingDetailsInfoEntity> list,Locale locale){
		messageBean.setLocale(null,null,locale);
		ResponseEntity<byte[]> response = null;
		//创建Excel对象
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		HSSFWorkbook workbook = new HSSFWorkbook();
		//输出Excel文件
		try {
			//创建工作表单
			HSSFSheet sheet = workbook.createSheet(messageBean.getMessage("file.name.packingDetail"));
			//创建HSSFRow对象 （行）第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
			HSSFRow row = sheet.createRow(0);
			row.setHeightInPoints(20);// 设备标题的高度
			//创建HSSFCell对象  （单元格）
			HSSFCell cell=null;
			// 样式定义
			// 字体设置
			HSSFFont fontStyle = workbook.createFont();
			fontStyle.setBold(true);//粗体显示
			// 表头样式
			HSSFCellStyle setBorder = workbook.createCellStyle();
			// 设置边框
			setBorder.setFont(fontStyle);
			// 数字单元格样式
			HSSFCellStyle numstyle = workbook.createCellStyle();
			numstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.000000"));
			//设置第一列单元格的列
			for(int i = 0; i < titles.length; i++){
				cell = row.createCell(i);//列索引从0开始
				cell.setCellValue(titles[i]);//列名1
				cell.setCellStyle(setBorder);
			};

			//设置单元格的值
			for (int i = 0; i < list.size(); i++) {
				row = sheet.createRow(i+1);
				MPackingDetailsInfoEntity example = list.get(i);
				//船期
				row.createCell(0).setCellValue(changeDate2Display(example.getDepartureDate(),"yyyy/MM/dd",locale));
				//装箱日期
				row.createCell(1).setCellValue(changeDate2Display(example.getPackingDate(),"yyyy/MM/dd",locale));
				//合同号
				row.createCell(2).setCellValue(StringUtil.isEmpty(example.getContractNo())?"":example.getContractNo());
				//供应商名称 StringUtil.isEmpty(date)
				row.createCell(3).setCellValue(StringUtil.isEmpty(example.getSupplierName())?"":example.getSupplierName());
				//物料号
				row.createCell(4).setCellValue(StringUtil.isEmpty(example.getMaterialCode())?"":example.getMaterialCode());
				//物料名称
				row.createCell(5).setCellValue(StringUtil.isEmpty(example.getMaterialNameCn())?"":example.getMaterialNameCn());
				//英文品名
				row.createCell(6).setCellValue(StringUtil.isEmpty(example.getMaterialNameEn())?"":example.getMaterialNameEn());
				//俄文品名
				row.createCell(7).setCellValue(StringUtil.isEmpty(example.getMaterialNameRu())?"":example.getMaterialNameRu());
				//HS CODE
				row.createCell(8).setCellValue(StringUtil.isEmpty(example.getHsNo())?"":example.getHsNo());
				//数量
				row.createCell(9).setCellValue(StringUtil.isEmpty(example.getNum())?"":example.getNum());
				//单位
				row.createCell(10).setCellValue(example.getDictUnit()!=null?example.getDictUnit().getName():"");
				//外包装件数
				row.createCell(11).setCellValue(StringUtil.isEmpty(example.getNumberOfPackages())?"":example.getNumberOfPackages());
				//净重
				row.createCell(12).setCellValue(StringUtil.isEmpty(example.getNetWeight())?"":example.getNetWeight());
				//毛重
				row.createCell(13).setCellValue(StringUtil.isEmpty(example.getGrossWeight())?"":example.getGrossWeight());
				//尺寸
				row.createCell(14).setCellValue(StringUtil.isEmpty(example.getSize())?"":example.getSize());
				//箱型
				row.createCell(15).setCellValue(StringUtil.isEmpty(example.getContainerSize())?"":example.getContainerSize());
				//箱号
				row.createCell(16).setCellValue(StringUtil.isEmpty(example.getCaseNumber())?"":example.getCaseNumber());
				//铅封号
				row.createCell(17).setCellValue(StringUtil.isEmpty(example.getLeadMark())?"":example.getLeadMark());
				//海运费
				row.createCell(18).setCellValue(StringUtil.isEmpty(example.getOceanFreight())?"":example.getOceanFreight());
				//单价
				row.createCell(19).setCellValue(StringUtil.isEmpty(example.getMaterialPrice())?"":example.getMaterialPrice());
				//总货值
				row.createCell(20).setCellValue(StringUtil.isEmpty(example.getTotalValue())?"":example.getTotalValue());
				//代理费
				row.createCell(21).setCellValue(StringUtil.isEmpty(example.getAgencyFee())?"":example.getAgencyFee());
				//集装箱重量
				row.createCell(22).setCellValue(StringUtil.isEmpty(example.getContainerWeight())?"":example.getContainerWeight());
				//主提单号
				row.createCell(23).setCellValue(StringUtil.isEmpty(example.getBillNo())?"":example.getBillNo());
				//分提单号
				row.createCell(24).setCellValue(StringUtil.isEmpty(example.getSecondaryBillNo())?"":example.getSecondaryBillNo());
				//船名航次
				row.createCell(25).setCellValue(StringUtil.isEmpty(example.getVesselNameVoyage())?"":example.getVesselNameVoyage());
				//港口
				row.createCell(26).setCellValue(StringUtil.isEmpty(example.getPort())?"":example.getPort());
				//目的港
				row.createCell(27).setCellValue(StringUtil.isEmpty(example.getDestinationPort())?"":example.getDestinationPort());
				//发运方式
				row.createCell(28).setCellValue(example.getDictShippingMethods()!=null?example.getDictShippingMethods().getName():"");
				//备注
				row.createCell(29).setCellValue(StringUtil.isEmpty(example.getRemark())?"":example.getRemark());

			}
			workbook.write(os);
			workbook.close();
			String filename_enc = UriUtils.encode(messageBean.getMessage("file.name.packingDetail"), "UTF-8");
			response = ResponseEntity.ok()
					.contentType(MediaType.parseMediaType("application/octet-stream"))
					.header("Access-Control-Expose-Headers","Content-Disposition")
					.header("Content-Disposition","attachment; filename*=UTF-8''" + filename_enc+".xlsx")
					.body(os.toByteArray());
			return response;
		} catch (Exception e) {
			throw new CommonBusinessException(e.getMessage().toString());
		}
	}

	/******************Private Methods***********************/
	/**
	 * 日期转换
	 * @return
	 */
    private String changeDate2Display(String str, String pattern,Locale locale) throws ParseException {
		messageBean.setLocale(null,null,locale);
    	SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
        Date date = null;
        String dateString = null;
        if(StringUtil.isEmpty(str)) {return dateString;}
        try {
        	if(str.contains("-")) {
        		str = str.replace("-", "/");
        	}
        	if(!str.contains("/")) {
        		dateString = str.substring(0,4)+"/"+str.substring(4,6)+"/"+str.substring(6,8);
        	}else {
        		date = dateFormat.parse(str);
                SimpleDateFormat formatter = new SimpleDateFormat(pattern);
                dateString = formatter.format(date);
        	}
        } catch (ParseException e) {
            throw new ParseException(messageBean.getMessage("common.excel.date.error",CommonConstantEnum.ORDER_DATE.getTypeName(locale)),0);
        }
        return dateString;
    }

	/***
	 * Excel文件解析
	 * @throws Exception
	 */
	private List<MPackingDetailsInfoEntity> resolvExcelToDb(MultipartFile excleFile,Authentication auth,String lang,Locale locale) throws NullPointerException,Exception{
		messageBean.setLocale(null,null,locale);
		try {
			SecurityUserInfoEntity principal = (SecurityUserInfoEntity)auth.getPrincipal();
			MRoleInfoEntity roleInfoEntity = mroleInfoDao.selectRoleInfo(principal.getRoleIdSelected(),"");
			List<MPackingDetailsInfoEntity> insertResultLst = new ArrayList<MPackingDetailsInfoEntity>();

			File file = CommonPoiReadUtil.MultipartFileToFile(excleFile);
			CommonPoiReadUtil poiUtil=new CommonPoiReadUtil(file,true);
			CommonExcelConfig config=new CommonExcelConfig();

			//设置读取参数
			poiUtil.setReplenishNull(false);
			poiUtil.setPrintLog(false);

			// 必须项[全部]
			config.setNotNullColumn(new int[]{});
			// 不需要快读
			config.setBriefRead(false);
			config.setStartRow(2);

			List<Map<String, Object>> dataLst=poiUtil.readExcel(config);
			//文件解析后数据放入实体对象List
			for(Map<String, Object> mapData:dataLst) {
				MPackingDetailsInfoEntity mPackingDetailsInfoEntity = new MPackingDetailsInfoEntity();
				// 主键ID
				mPackingDetailsInfoEntity.setId(CommonSqlUtils.getUUID32());
				//船期
				mPackingDetailsInfoEntity.setDepartureDate(parseDate((String)mapData.get("船期"),"yyyyMMdd",locale));
				//装箱日期
				mPackingDetailsInfoEntity.setPackingDate(parseDate((String)mapData.get("装箱日期"),"yyyyMMdd",locale));
				//合同号
				mPackingDetailsInfoEntity.setContractNo((String)mapData.get("合同号"));
				//供应商名称
				mPackingDetailsInfoEntity.setSupplierName((String)mapData.get("供应商名称"));
				//物料号
				mPackingDetailsInfoEntity.setMaterialCode((String)mapData.get("物料号"));
				//物料名称
				mPackingDetailsInfoEntity.setMaterialNameCn((String)mapData.get("物料名称"));
				//英文品名
				mPackingDetailsInfoEntity.setMaterialNameEn((String)mapData.get("英文品名"));
				//俄文品名
				mPackingDetailsInfoEntity.setMaterialNameRu((String)mapData.get("俄文品名"));
				//HS CODE
				mPackingDetailsInfoEntity.setHsNo((String)mapData.get("HS CODE"));
				//数量
				mPackingDetailsInfoEntity.setNum((String)mapData.get("数量"));
				//单位
//				mPackingDetailsInfoEntity.setUnit((String)mapData.get("单位"));
				String unit = getFromDictDataByName(
						(String)mapData.get("外包装单位"),"packagingUnit","外包装单位",lang,locale);
				mPackingDetailsInfoEntity.setUnit(unit);
				//外包装件数
				mPackingDetailsInfoEntity.setNumberOfPackages((String)mapData.get("外包装件数"));
				//净重
				if (mapData.get("净重") != null && StringUtil.isNotEmpty((String)mapData.get("净重"))){
					BigDecimal bd=new BigDecimal((String)mapData.get("净重"));
					bd=bd.setScale(6, BigDecimal.ROUND_HALF_UP);
					mPackingDetailsInfoEntity.setNetWeight(bd.toString());
				}else {
					mPackingDetailsInfoEntity.setNetWeight("0.000000");
				}
				//毛重
				if (mapData.get("毛重") != null && StringUtil.isNotEmpty((String)mapData.get("毛重"))){
					BigDecimal bd=new BigDecimal((String)mapData.get("毛重"));
					bd=bd.setScale(6, BigDecimal.ROUND_HALF_UP);
					mPackingDetailsInfoEntity.setGrossWeight(bd.toString());
				}else {
					mPackingDetailsInfoEntity.setGrossWeight("0.000000");
				}
//				mPackingDetailsInfoEntity.setGrossWeight((String)mapData.get("毛重"));
				//尺寸
                if (mapData.get("尺寸") != null && StringUtil.isNotEmpty((String)mapData.get("尺寸"))){
                    BigDecimal bd=new BigDecimal((String)mapData.get("尺寸"));
                    bd=bd.setScale(6, BigDecimal.ROUND_HALF_UP);
                    mPackingDetailsInfoEntity.setSize(bd.toString());
                }else {
                    mPackingDetailsInfoEntity.setSize("0.000000");
                }
//				mPackingDetailsInfoEntity.setSize((String)mapData.get("尺寸"));
				//箱型
				mPackingDetailsInfoEntity.setContainerSize((String)mapData.get("箱型"));
				//箱号
				mPackingDetailsInfoEntity.setCaseNumber((String)mapData.get("箱号"));
				//铅封号
				mPackingDetailsInfoEntity.setLeadMark((String)mapData.get("铅封号"));
				//海运费
                if (mapData.get("海运费") != null && StringUtil.isNotEmpty((String)mapData.get("海运费"))){
                    BigDecimal bd=new BigDecimal((String)mapData.get("海运费"));
                    bd=bd.setScale(6, BigDecimal.ROUND_HALF_UP);
                    mPackingDetailsInfoEntity.setOceanFreight(bd.toString());
                }else {
                    mPackingDetailsInfoEntity.setOceanFreight("0.000000");
                }
//				mPackingDetailsInfoEntity.setOceanFreight((String)mapData.get("海运费"));
				//单价
                if (mapData.get("单价") != null && StringUtil.isNotEmpty((String)mapData.get("单价"))){
                    BigDecimal bd=new BigDecimal((String)mapData.get("单价"));
                    bd=bd.setScale(6, BigDecimal.ROUND_HALF_UP);
                    mPackingDetailsInfoEntity.setMaterialPrice(bd.toString());
                }else {
                    mPackingDetailsInfoEntity.setMaterialPrice("0.000000");
                }
//				mPackingDetailsInfoEntity.setMaterialPrice((String)mapData.get("单价"));
				//总货值
                if (mapData.get("总货值") != null && StringUtil.isNotEmpty((String)mapData.get("总货值"))){
                    BigDecimal bd=new BigDecimal((String)mapData.get("总货值"));
                    bd=bd.setScale(6, BigDecimal.ROUND_HALF_UP);
                    mPackingDetailsInfoEntity.setTotalValue(bd.toString());
                }else {
                    mPackingDetailsInfoEntity.setTotalValue("0.000000");
                }
//				mPackingDetailsInfoEntity.setTotalValue((String)mapData.get("总货值"));
				//代理费
                if (mapData.get("代理费") != null && StringUtil.isNotEmpty((String)mapData.get("代理费"))){
                    BigDecimal bd=new BigDecimal((String)mapData.get("代理费"));
                    bd=bd.setScale(6, BigDecimal.ROUND_HALF_UP);
                    mPackingDetailsInfoEntity.setAgencyFee(bd.toString());
                }else {
                    mPackingDetailsInfoEntity.setAgencyFee("0.000000");
                }
//				mPackingDetailsInfoEntity.setAgencyFee((String)mapData.get("代理费"));
				//集装箱重量
                if (mapData.get("集装箱重量") != null && StringUtil.isNotEmpty((String)mapData.get("集装箱重量"))){
                    BigDecimal bd=new BigDecimal((String)mapData.get("集装箱重量"));
                    bd=bd.setScale(6, BigDecimal.ROUND_HALF_UP);
                    mPackingDetailsInfoEntity.setContainerWeight(bd.toString());
                }else {
                    mPackingDetailsInfoEntity.setContainerWeight("0.000000");
                }
//				mPackingDetailsInfoEntity.setContainerWeight((String)mapData.get("集装箱重量"));
				//主提单号
				mPackingDetailsInfoEntity.setBillNo((String)mapData.get("主提单号"));
				//分提单号
				mPackingDetailsInfoEntity.setSecondaryBillNo((String)mapData.get("分提单号"));
				//船名航次
				mPackingDetailsInfoEntity.setVesselNameVoyage((String)mapData.get("船名航次"));
				//港口
				mPackingDetailsInfoEntity.setPort((String)mapData.get("港口"));
				//目的港
				mPackingDetailsInfoEntity.setDestinationPort((String)mapData.get("目的港"));
				//发运方式
//				mPackingDetailsInfoEntity.setShippingMethods((String)mapData.get("发运方式"));
				String shippingMethods = getFromDictDataByName(
						(String)mapData.get("发运方式"),"transportation","运输方式",lang,locale);
				mPackingDetailsInfoEntity.setShippingMethods(shippingMethods);
				//备注
				mPackingDetailsInfoEntity.setRemark((String)mapData.get("备注"));
				// 创建者
				mPackingDetailsInfoEntity.setCreateUser(principal.getUserName());
				mPackingDetailsInfoEntity.setIsDelete(0);
				mPackingDetailsInfoEntity.setVersion(1);
				insertResultLst.add(mPackingDetailsInfoEntity);
			}
			return insertResultLst;
		}
		catch(NullPointerException se) {
			throw se;
		}catch(Exception e) {
			throw e;
		}
	}

	/***
	 * 根据Excel中的文字内容匹配字典数据，取得对应的值
	 * @param nameValue
	 * @param dictType
	 * @return
	 */
	private String getFromDictDataByName(String nameValue,String dictType,String dictTypeCn,String lang,Locale locale) {
		messageBean.setLocale(null,null,locale);
		TDictDataEntity dictDataParam = new TDictDataEntity();
		try {
			dictDataParam.setName(nameValue);
			dictDataParam.setDictTypeCode(dictType);
			dictDataParam.setLanguageFlag(lang);
			List<TDictDataEntity> dictDataLst = tDictDataDao.selectByPrimaryKey(dictDataParam);
			if(dictDataLst.size()>0) {
				return dictDataLst.get(0).getValue();
			}else {
				throw new NullPointerException(messageBean.getMessage("common.dict.emptyerror", dictTypeCn));
			}
		}
		catch(Exception e) {
			throw e;
		}
	}

	/***
	 * 日期转换
	 * @param str
	 * @param pattern
	 * @return date
	 * @throws ParseException
	 */
	private String parseDate(String str, String pattern,Locale locale) throws ParseException {
		messageBean.setLocale(null,null,locale);
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
		Date date = null;
		String dateString = null;
		if(StringUtil.isEmpty(str)) {return dateString;}
		try {
        	if(str.contains("-")) {
        		str = str.replace("-", "/");
        	}
			date = dateFormat.parse(str);
			SimpleDateFormat formatter = new SimpleDateFormat(pattern);
			dateString = formatter.format(date);
		} catch (ParseException e) {
			throw new ParseException(messageBean.getMessage("common.excel.date.error",CommonConstantEnum.ORDER_DATE.getTypeName(locale)),0);
		}
		return dateString;
	}
}
